use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Candidat_VacancyHistory')
  drop table Candidat_VacancyHistory
go

create table Candidat_VacancyHistory
(
  Id int identity(1,1) not null,
  CandidatId int not null,
  VacancyId int not null,
  SendResumeDate datetime null,
  InterviewDate datetime null,
  InterviewResultId int null,
  ResultDate datetime null,
  IsAutoAdded bit not null 
)  
go

alter table Candidat_VacancyHistory add constraint PK_Candidat_VacancyHistory_ID primary key(Id)
go


alter table Candidat_VacancyHistory add constraint FK_Candidat_VacancyHistory_Candidates_CandidatId 
      foreign key (CandidatId) references Candidates(Id) on delete cascade
go


alter table Candidat_VacancyHistory add constraint FK_Candidat_VacancyHistory_Vacancies_VacancyId 
      foreign key (VacancyId) references Vacancies(Id) 
go


alter table Candidat_VacancyHistory add constraint FK_Candidat_VacancyHistory_InterviewResults_InterviewResultID 
      foreign key (InterviewResultId) references InterviewResults(Id) on delete cascade
go


create nonclustered index IX_Candidat_VacancyHistory_CandidatId on Candidat_VacancyHistory
       (CandidatId asc)
go

create nonclustered index IX_Candidat_VacancyHistory_VacancyId on Candidat_VacancyHistory
       (VacancyID asc)
go


alter table Candidat_VacancyHistory with check add constraint DF_Candidat_VacancyHistory_IsAutoAdded
      default (0) for IsAutoAdded
go